const { Service } = require("egg");

class ScoreService extends Service {
  /**
   * 查询用户今日成绩
   * @param {number} uid 用户id
   */
  async readByToday(uid) {
    let sql = `
            select id, theory, skill, create_time
            from score
            where uid = ? and date_format(create_time, '%Y-%m-%d') = ?
            limit 0, 1
        `;
    // 获取今天的日期格式，如：2020-05-21
    let today = this.ctx.helper.dateFormat(new Date());
    let params = [uid, today];
    let res = await this.app.mysql.query(sql, params);
    return res[0];
  }

  /**
   * 添加成绩
   * @param {number} uid 用户id
   * @param {number} theory 理论成绩
   * @param {number} skill 技能成绩
   */
  async create({ uid, theory, skill }) {
    let sql = `insert into score (uid, theory, skill) values (?, ?, ?)`;
    let params = [uid, theory, skill];
    return await this.app.mysql.query(sql, params);
  }

  /**
   * 获取成绩列表
   * @param {number} pagesize 一页个数
   * @param {number} pagenum 页码
   * @param {number} query 模糊匹配字符串
   * @param {number} create_time 录入时间
   */
  async readListByQuery({ pagesize, pagenum, query, create_time }) {
    let select_sql =
      "select s.id, s.theory, s.skill, s.uid, s.create_time, u.username, u.relname";
    let from_sql = " from score as s";
    let join_sql = " join user as u on u.id = s.uid";
    let where_sql = "";
    let params = [];
    // 条件查询
    if (query) {
      where_sql += " where u.relname like ?";
      params.push("%" + query + "%");
    }
    // 查询当日成绩列表
    if (create_time) {
      // 获取今日时间格式
      let today = this.ctx.helper.dateFormat(new Date(create_time));
      // 如果之前已经有了 where 语句，这里就需要 and 连接
      where_sql += `${
        query ? " and" : " where"
      } date_format(s.create_time, '%Y-%m-%d') = ?`;
      params.push(today);
    }
    // 分页参数
    if (pagenum && pagesize) {
      // 统计总数
      let count_sql = "select count(*) as count";
      // 统计总数
      const countArr = await this.app.mysql.query(
        count_sql + from_sql + join_sql + where_sql,
        params
      );
      // 分页
      let offset = (pagenum - 1) * pagesize;
      params.push(offset, parseInt(pagesize));
      const dataArr = await this.app.mysql.query(
        select_sql + from_sql + join_sql + where_sql + " limit ?, ?",
        params
      );
      return {
        total: countArr[0].count,
        list: dataArr,
      };
    }
    // 没有分页，直接查询结果
    return await this.app.mysql.query(
      select_sql + from_sql + join_sql + where_sql,
      params
    );
  }

  /**
   * 更新一条记录
   * @param {Object} param0 参数值
   */
  async update({ id, theory, skill }) {
    const row = { id };

    // 需要更新理论
    if (theory) {
      row.theory = theory;
    }

    // 需要更新技能
    if (skill) {
      row.skill = skill;
    }

    return await this.app.mysql.update("score", row);
  }

  /**
   * 删除成绩
   * @param {Number} id 成绩记录 id
   */
  async deleteById(id) {
    const res = await this.app.mysql.delete("score", { id });
    if (res.affectedRows < 1) {
      this.ctx.throwRE(40401, "删除失败，不存在该记录");
    }
  }
}

module.exports = ScoreService;
